Pivot longer

The function pivot_longer() longifys data, converting it from wide to long. Long data is generally the preferred format for Tidyverse, and is especially useful for ggplot2.

When data is longified:

• The number of rows increases.
• The number of columns decreases.

To longify data a set of columns are chosen.

• The column names are assigned as data of a names column.
• The values within the columns are assigned to a value column. Each value is within its own row.

Tidyverse reference page

Dataset

For demonstration we’ll load the fisheries_wide_tbl data from the mgrtibbles package (hyperlink includes install instructions).

fisheries_wide_tbl details

#Load package
library("mgrtibbles")
#fisheries_wide_tbl tibble for demonstration
mgrtibbles::fisheries_wide_tbl |>
    #View first 11 columns with select
    dplyr::select(1:11)

# A tibble: 264 × 11
   Entity  `1969` `1970` `1971` `1972` `1973` `1974` `1975` `1976` `1977` `1978`
   <chr>    <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>
 1 Afgha…     400 4   e2 5   e2 5   e2 5   e2 5   e2 6   e2 6   e2 6   e2 6   e2
 2 Afric… 3078461 2.48e6 2.36e6 2.63e6 2.78e6 2.74e6 2.49e6 2.20e6 2.16e6 2.12e6
 3 Afric…  850772 9.61e5 1.13e6 1.20e6 1.20e6 1.24e6 1.28e6 1.26e6 1.29e6 1.31e6
 4 Alban…    7000 8   e3 8   e3 8   e3 8.00e3 8.45e3 8.41e3 8.38e3 8.34e3 8.81e3
 5 Alger…   23150 2.42e4 2.37e4 2.83e4 3.12e4 3.58e4 3.77e4 3.51e4 4.35e4 3.41e4
 6 Ameri…       0 0      0      0      1   e2 8.2 e1 1.36e2 1.13e2 2.02e2 1.93e2
 7 Andor…      NA 0      0      0      0      0      0      0      0      0     
 8 Angola  425200 3.75e5 3.24e5 6.07e5 4.79e5 4.00e5 1.61e5 8.15e4 1.20e5 1.26e5
 9 Antig…     800 9   e2 9   e2 1.2 e3 1.5 e3 1.59e3 1.60e3 1.61e3 1.82e3 1.62e3
10 Arab …  634609 6.88e5 7.05e5 7.53e5 9.21e5 8.99e5 8.64e5 9.34e5 9.05e5 9.10e5
# ℹ 254 more rows

Wide to long

Pivot the wide tibble to a long tibble.

The three options provided below are:

• The columns to use for longifying.
  • Below !Entity is used to longify all but the Entity column.
  • String vectors can be used.
• names_to=: New column in long data containing the names of the longified columns.
• values_to=: New column in long data containing the values of the longified columns.

fisheries_wide_tbl |> 
    tidyr::pivot_longer(!Entity, names_to="Year", values_to = "Metric_tons")

# A tibble: 15,576 × 3
   Entity      Year  Metric_tons
   <chr>       <chr>       <dbl>
 1 Afghanistan 1969          400
 2 Afghanistan 1970          400
 3 Afghanistan 1971          500
 4 Afghanistan 1972          500
 5 Afghanistan 1973          500
 6 Afghanistan 1974          500
 7 Afghanistan 1975          600
 8 Afghanistan 1976          600
 9 Afghanistan 1977          600
10 Afghanistan 1978          600
# ℹ 15,566 more rows

More metadata columns

What if we have more than one metadata column such as in the world_pop_wide_tbl displayed below?

world_pop_wide_tbl

# A tibble: 234 × 13
   CCA3  `Country/Territory` Capital       Continent `2022` `2020` `2015` `2010`
   <fct> <fct>               <chr>         <fct>      <dbl>  <dbl>  <dbl>  <dbl>
 1 AFG   Afghanistan         Kabul         Asia      4.11e7 3.90e7 3.38e7 2.82e7
 2 ALB   Albania             Tirana        Europe    2.84e6 2.87e6 2.88e6 2.91e6
 3 DZA   Algeria             Algiers       Africa    4.49e7 4.35e7 3.95e7 3.59e7
 4 ASM   American Samoa      Pago Pago     Oceania   4.43e4 4.62e4 5.14e4 5.48e4
 5 AND   Andorra             Andorra la V… Europe    7.98e4 7.77e4 7.17e4 7.15e4
 6 AGO   Angola              Luanda        Africa    3.56e7 3.34e7 2.81e7 2.34e7
 7 AIA   Anguilla            The Valley    North Am… 1.59e4 1.56e4 1.45e4 1.32e4
 8 ATG   Antigua and Barbuda Saint John’s  North Am… 9.38e4 9.27e4 8.99e4 8.57e4
 9 ARG   Argentina           Buenos Aires  South Am… 4.55e7 4.50e7 4.33e7 4.11e7
10 ARM   Armenia             Yerevan       Asia      2.78e6 2.81e6 2.88e6 2.95e6
# ℹ 224 more rows
# ℹ 5 more variables: `2000` <dbl>, `1990` <dbl>, `1980` <dbl>, `1970` <dbl>,
#   Area_km2 <dbl>

In this case in it easier to specify which columns we want longified. That is columns 2022 to 1970.

world_pop_wide_tbl |> 
    tidyr::pivot_longer(`2022`:`1970`, names_to="Year", values_to="Population")

# A tibble: 1,872 × 7
   CCA3  `Country/Territory` Capital Continent Area_km2 Year  Population
   <fct> <fct>               <chr>   <fct>        <dbl> <chr>      <dbl>
 1 AFG   Afghanistan         Kabul   Asia        652230 2022    41128771
 2 AFG   Afghanistan         Kabul   Asia        652230 2020    38972230
 3 AFG   Afghanistan         Kabul   Asia        652230 2015    33753499
 4 AFG   Afghanistan         Kabul   Asia        652230 2010    28189672
 5 AFG   Afghanistan         Kabul   Asia        652230 2000    19542982
 6 AFG   Afghanistan         Kabul   Asia        652230 1990    10694796
 7 AFG   Afghanistan         Kabul   Asia        652230 1980    12486631
 8 AFG   Afghanistan         Kabul   Asia        652230 1970    10752971
 9 ALB   Albania             Tirana  Europe       28748 2022     2842321
10 ALB   Albania             Tirana  Europe       28748 2020     2866849
# ℹ 1,862 more rows

Note: Quotes are used for 2022 and 1970 as column/variable names that are numbers need them or they will not work.